﻿using System;
using System.Collections.ObjectModel;
using System.Data.SqlClient;
using MySql.Data.MySqlClient;
using Reviewer.Data.Bases;
using Reviewer.Entities;
using Reviewer.Utilities.Data;
using System.Globalization;
using System.Collections.Generic;

namespace Reviewer.Data
{
    /// <summary>
    /// Game repository
    /// </summary>
    [CLSCompliant(false)]
    public class GameRepository : RepositoryCrudAlternativeBase<Game, Int32, Guid>
    {
        #region fields (static)
        private const string TABLE_NAME = "V2_Spil";
        private static readonly CrudColumnInfo columnId = new CrudColumnInfo(0, "Id", MySqlDbType.Int32, true, false, false, true);
        private static readonly CrudColumnInfo columnGameId = new CrudColumnInfo(1, "SpilId", MySqlDbType.VarChar, 36, false, true, false);
        private static readonly CrudColumnInfo columnTitle = new CrudColumnInfo(2, "Titel", MySqlDbType.VarChar, 255, false, false, true);
        private static readonly CrudColumnInfo columnPublisher = new CrudColumnInfo(3, "Udgiver", MySqlDbType.VarChar, 255, false, false, true);
        private static readonly CrudColumnInfo columnDeveloper = new CrudColumnInfo(4, "Udvikler", MySqlDbType.VarChar, 255, false, false, true);
        private static readonly CrudColumnInfo columnGenre = new CrudColumnInfo(5, "Genre", MySqlDbType.Int32, false, false, true);
        private static readonly CrudColumnInfo columnMultiplayerInfo = new CrudColumnInfo(6, "MultiPlayerInfo", MySqlDbType.Int32, false, false, true);
        private static readonly CrudColumnInfo columnFormat = new CrudColumnInfo(7, "Format", MySqlDbType.VarChar, 255, false, false, true);
        private static readonly CrudColumnInfo columnReleaseDate = new CrudColumnInfo(8, "ReleaseDato", MySqlDbType.DateTime, false, false, true);
        private static readonly CrudColumnInfo columnUpdateDate = new CrudColumnInfo(9, "UpdateDato", MySqlDbType.DateTime, false, false, true);
        private static readonly CrudColumnInfo columnReleaseDateFormat = new CrudColumnInfo(9, "ReleaseDateFormat", MySqlDbType.Int16, false, false, true);
        #endregion

        #region methods (public)
        /// <summary>
        /// Selects the top number of items based on release date.
        /// </summary>
        /// <param name="topCount">The top count.</param>
        /// <param name="ascending">if set to <c>true</c> [ascending].</param>
        /// <returns></returns>
        public Collection<Game> SelectTopOnReleaseDate(int topCount, bool ascending)
        {
            return base.SelectAll(topCount, GameRepository.columnReleaseDate, ascending ? SortOrder.Ascending : SortOrder.Descending);
        }

        /// <summary>
        /// Selects games with missing screenshots.
        /// </summary>
        /// <returns></returns>
        public Collection<Game> SelectWithMissingScreenshots()
        {
            using (MySqlCommand command = new MySqlCommand(
                this.SelectAllQuery() + " WHERE Id IN (SELECT G.Id FROM " + this.TableName + " G LEFT OUTER JOIN (SELECT SpilId, Format FROM V2_Screenshots GROUP BY SpilId, Format) GS ON (GS.SpilId = G.SpilId AND GS.Format = G.Format) WHERE GS.SpilId IS NULL)"
                ))
            {
                return base.ExecuteReaderAndBuildList(command);
            }
        }

        public Collection<Game> Find(string title)
        {
            using (MySqlCommand command = new MySqlCommand(
                String.Format(CultureInfo.InvariantCulture
                , "{0} WHERE {1} LIKE @{1}"
                , this.SelectAllQuery(),
                columnTitle.Name
                )))
            {
                columnTitle.AddParameter(command, "%" + title + "%");
                return base.ExecuteReaderAndBuildList(command);
            }
        }

        public Collection<Game> SelectByNewsRelation(Guid newsId)
        {

            using (MySqlCommand command = new MySqlCommand(String.Format(
                CultureInfo.InvariantCulture
                , "{0} LEFT OUTER JOIN V2_NewsRelations R ON (N.SpilId = R.SpilId) WHERE R.NewsId = @NewsId"
                , this.SelectAllQuery("N"))))
            {
                command.Parameters.AddWithValue("@NewsId", newsId.ToString());
                return base.ExecuteReaderAndBuildList(command);
            }
        }

        public ICollection<Game> Search(string titleSearch, Int32? format, int maxResults)
        {
            using (MySqlCommand command = new MySqlCommand(this.SelectAllQuery()))
            {
                command.CommandText += " WHERE 1=1";
                if (!String.IsNullOrEmpty(titleSearch))
                {
                    command.CommandText += String.Format(CultureInfo.InvariantCulture, " AND {0} LIKE @{0}", GameRepository.columnTitle.Name);
                    GameRepository.columnTitle.AddParameter(command, titleSearch);
                }
                if (format.HasValue && format.Value > 0)
                {
                    command.CommandText += String.Format(CultureInfo.InvariantCulture, " AND {0} = @{0}", GameRepository.columnFormat.Name);
                    GameRepository.columnFormat.AddParameter(command, format.Value);
                }
                command.CommandText += " ORDER BY Id DESC";

                if (maxResults <= 0)
                    maxResults = 1000;

                command.CommandText += " LIMIT " + maxResults.ToString(CultureInfo.InvariantCulture);

                return base.ExecuteReaderAndBuildList(command);
            }
        }

        public ICollection<Game> SearchFutureReleases(int? releaseMonth, int? format, int maxResults)
        {
            using (MySqlCommand command = new MySqlCommand(this.SelectAllQuery()))
            {
                command.CommandText += String.Format(CultureInfo.InvariantCulture,
                    " WHERE {0} >= Now()"
                    , columnReleaseDate.Name);

                if (releaseMonth.HasValue && releaseMonth.Value > 0 && releaseMonth.Value < 13)
                {
                    command.CommandText += String.Format(CultureInfo.InvariantCulture,
                        " AND MONTH({0}) = @{0}"
                        , columnReleaseDate.Name);
                    command.Parameters.AddWithValue(String.Format(CultureInfo.InvariantCulture, "@{0}", columnReleaseDate.Name), releaseMonth.Value);
                }

                if (format.HasValue && format.Value > 0)
                {
                    command.CommandText += String.Format(CultureInfo.InvariantCulture
                        , " AND {0} = @{0}"
                        , columnFormat.Name);
                    columnFormat.AddParameter(command, format.Value);
                }
                command.CommandText += String.Format(CultureInfo.InvariantCulture, " ORDER BY {0}", columnReleaseDate.Name);

                if (maxResults <= 0)
                    maxResults = 1000;
                command.CommandText += " LIMIT " + maxResults.ToString(CultureInfo.InvariantCulture);

                return base.ExecuteReaderAndBuildList(command);
            }
        }

        public ICollection<Game> SelectByReleaseDate(int month, int year)
        {
            using (MySqlCommand cmd = new MySqlCommand(this.SelectAllQuery()))
            {
                cmd.CommandText += String.Format(CultureInfo.InvariantCulture,
                " WHERE {0} IS NOT NULL AND Year({0}) = @Year AND ("
                + " ({1} = 4)"
                + " OR ({1} = 3 AND FLOOR(((Month({0})-1)/3))+1 = FLOOR(((@Month-1)/3))+1)"
                + " OR (Month({0}) = @Month))"
                + " ORDER BY {1} ASC, {0} ASC"
                + " LIMIT 1000",
                columnReleaseDate.Name,
                columnReleaseDateFormat.Name);
                cmd.Parameters.AddWithValue("@Month", month);
                cmd.Parameters.AddWithValue("@Year", year);

                return this.ExecuteReaderAndBuildList(cmd);
                //WHERE ReleaseDato IS NOT NULL AND Year(ReleaseDato) = 2010 AND ((ReleaseDateFormat = 3) OR (ReleaseDateFormat = 2 AND FLOOR(((Month(ReleaseDato)-1)/3)) + 1 = 2) OR (Month(ReleaseDato) = 8))
            }
        }

        #endregion

        #region methods (protected)
        internal override CrudColumnInfo[] Columns
        {
            get
            {
                return new CrudColumnInfo[]{
                    GameRepository.columnId
                    , GameRepository.columnGameId
                    , GameRepository.columnTitle
                    , GameRepository.columnPublisher
                    , GameRepository.columnDeveloper
                    , GameRepository.columnGenre
                    , GameRepository.columnMultiplayerInfo
                    , GameRepository.columnFormat
                    , GameRepository.columnReleaseDate
                    , GameRepository.columnUpdateDate
                    , GameRepository.columnReleaseDateFormat
                };
            }
        }

        internal override object ColumnToValue(CrudColumnInfo info, Game item)
        {
            if (info.Equals(GameRepository.columnId))
                return item.Id;
            else if (info.Equals(GameRepository.columnGameId))
                return item.GameId;
            else if (info.Equals(GameRepository.columnTitle))
                return item.Title;
            else if (info.Equals(GameRepository.columnPublisher))
                return item.Publisher;
            else if (info.Equals(GameRepository.columnDeveloper))
                return item.Developer;
            else if (info.Equals(GameRepository.columnGenre))
                return item.Genre;
            else if (info.Equals(GameRepository.columnMultiplayerInfo))
                return item.MultiplayerInfo;
            else if (info.Equals(GameRepository.columnFormat))
                return item.Format;
            else if (info.Equals(GameRepository.columnReleaseDate))
                return item.ReleaseDate;
            else if (info.Equals(GameRepository.columnUpdateDate))
                return item.UpdateDate;
            else if (info.Equals(GameRepository.columnReleaseDateFormat))
                return item.ReleaseDateFormat;
            return null;
        }

        protected override string TableName
        {
            get { return GameRepository.TABLE_NAME; }
        }

        protected override Game BuildItemFromReader(System.Data.IDataReader reader)
        {
            Game item = new Game();
            if (reader != null)
            {
                item.Id = DataReaderUtility.GetValue(reader, reader.GetOrdinal(GameRepository.columnId.Name), 0);
                item.GameId = DataReaderUtility.GetValue(reader, reader.GetOrdinal(GameRepository.columnGameId.Name), Guid.Empty);
                item.Title = DataReaderUtility.GetValue(reader, reader.GetOrdinal(GameRepository.columnTitle.Name), String.Empty);
                item.Publisher = DataReaderUtility.GetValue(reader, reader.GetOrdinal(GameRepository.columnPublisher.Name), String.Empty);
                item.Developer = DataReaderUtility.GetValue(reader, reader.GetOrdinal(GameRepository.columnDeveloper.Name), String.Empty);
                item.Genre = DataReaderUtility.GetValue(reader, reader.GetOrdinal(GameRepository.columnGenre.Name), 0);
                item.Format = DataReaderUtility.GetValue(reader, reader.GetOrdinal(GameRepository.columnFormat.Name), 0);
                item.MultiplayerInfo = DataReaderUtility.GetValue(reader, reader.GetOrdinal(GameRepository.columnMultiplayerInfo.Name), 0);
                item.ReleaseDate = DataReaderUtility.GetValue(reader, reader.GetOrdinal(GameRepository.columnReleaseDate.Name), DateTime.MinValue);
                item.UpdateDate = DataReaderUtility.GetValue(reader, reader.GetOrdinal(GameRepository.columnUpdateDate.Name), DateTime.MinValue);
                item.ReleaseDateFormat = DataReaderUtility.GetValue(reader, reader.GetOrdinal(GameRepository.columnReleaseDateFormat.Name), 1);
            }
            return item;
        }
        #endregion
    }
}
